<?php
/**
  * Musashi - A project manager for music groups and bands
  * Copyright (C) 2010 Daniel Torres
  *
  * This program is free software: you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
  * the Free Software Foundation, either version 3 of the License, or
  * (at your option) any later version.
  *
  * This program is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.
  *
  * You should have received a copy of the GNU General Public License
  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  *
  */

/**
 *  Catalog database model. Controls the available database listings. 
 *  
 * @package Musashi
 * @author Daniel Torres
 */
 
// Make sure we have access to this page
defined('_JEXEC') or die ('Restricted access');
jimport('joomla.application.component.model');

/**
 *  
 * @author Daniel Torres
 *
 * Controls transactions for the atomic tables of our database. For a table
 * to properly work with this system, you must:
 *
 *    1. Add an entry into the $sections array, to link a request name into
 *       a database table name.
 *    2. Add a default_<name>.php file into the /views/catalog/tmpl folder, to
 *       display table information when requested.
 *    3. Add a <name>edit.php file into the same directory, to add/edit entries
 *    4. Add a <name>.php file into the /tables directory, to handle the
 *       sql transactions.
 * 
 */
 class MusashiAdminModelCatalog extends JModel
 {
   /**
    * Database binding strings
    * 
    * These control the known database names and their identifiers. For each
    * entry, a view template of the same name is expected. Add or remove
    * as required.
    */
   
   var $sections = array(
    // name                       database                      page title
       "venues"         => array("#__Musashi_Venue",            "venues"            ),
       "songs"          => array("#__Musashi_Song",             "songs"             ),
       "positions"      => array("#__Musashi_Position",         "positions"         ),
       "equipmenttype"  => array("#__Musashi_EquipmentType",    "equipment types"   ),
       "clothingtype"   => array("#__Musashi_ClothingType",     "clothing types"    ),
       "clothing"       => array("#__Musashi_Clothing",         "clothing"          ),
       "equipment"      => array("#__Musashi_Equipment",        "equipment"         ),
       "costumes"       => array("#__Musashi_Costume",          "costumes"          ),
       "roles"          => array("#__Musashi_Role",             "roles"             ),
   );
      
   /**
    *  Private vars
    */
    
   var $_sectionData    = null;    // Database rows for requested section
   var $_recordData     = null;    // Database record
   var $_currentSection = null;    // Whatever we're currently looking at
   var $_currentSectTtl = null;    // Reader-friendly version of what we are looking at
   var $_sortOrder      = array(); // Sorting order, if any

   /**
    * Queries the db to obtain the requested entries, given the user-selected table.
    * This is the first function to be called. To manually set a section, call
    * setSection directly. Otherwise, this function reads the section from the
    * sectn var on the url.
    */
   function readCatalogEntries()
   {
      // Get the current section
      $this->readSection();
      
      // Set the current record list to the requested one
      $this->refreshSectionData();
   }
   
   /**
    * Queries the db to obtain a particular entry, given the current section and
    * a provided ID. If you want to get all entries, call readCatalogEntries instead
    */
   function readCatalogRecord()
   {
      // Get the current section
      $this->readSection();

      // Refresh the catalog id
      $this->refreshRecordData();   
   }
   
   /**
    * Reads the section back from our post url
    */
   function readSection()
   {
      $requestedSection = JRequest::getString('sectn');
      $this->_currentSection = $requestedSection;        
   }
     
   /**
    *  Returns all records for a given section, or null if something goes wrong
    *  @param $section        A key value of the $sections array
    *  @param $queriedFields  A string of requested values. It can be any sql card
    */
   function getRecordsForSection( $section, $queriedFields, $orderBy = "" )
   {
      // Make sure the current section is a valid one
      if( array_key_exists($section,$this->sections))
      {
         // Generate a query to obtain all records
         $query = 'SELECT '.$queriedFields.' FROM '.$this->sections[$section][0];
         
         // Ordering
         if( $orderBy != "" ){
          $query .= " ORDER BY ".$this->sections[$section][0].".".$orderBy;
         }
         return $this->_getList( $query );
      }
      else
      {
        $this->setError('The requested register ['.$section.'] is unknown');
        return null;
      }      
    
   }

      
   /**
     *   Creates an sql query to return all rows for the given query
     */
    function refreshSectionData()
    {
      // Find out if we must change the ordering of data
      $sorting = "";
      $this->_sortOrder[] = JRequest::getVar('filter_order','','post','cmd');
      $this->_sortOrder[] = JRequest::getVar('filter_order_Dir','','post','word');
      
      if( $this->_sortOrder[0] != "" && $this->_sortOrder[1] != "" ){
        $sorting = $this->_sortOrder[0]." ".$this->_sortOrder[1]." ";
      }
      
      // Get the requested records.
      $this->_sectionData = $this->getRecordsForSection($this->_currentSection,'*',$sorting);
      
      // Get the title, if available.  This key search is redundant, but
      // necessary, because if sectionData is null, that might mean the table
      // is just empty
      if( array_key_exists($this->_currentSection, $this->sections ) ){
        $this->_currentSectTtl = $this->sections[$this->_currentSection][1];
      }
      
    }
       
   /**
     *   Reads a record entry from a given table. The section must be defined.
     *   Requires the table name and record id
     */
    function refreshRecordData()
    {
      if(empty($this->_recordData))
      {         
         // Make sure the requested section exists
         if( array_key_exists($this->_currentSection,$this->sections))
         {
            // Get an ID
            $idArray = JRequest::getVar('cid',0,'','array');
            $id      = (int)$idArray[0];
            
            // Obtain the requested record
            $query = 'SELECT * FROM '.$this->sections[$this->_currentSection][0].' WHERE id = '.$id;
            $this->_db->setQuery($query);
            $this->_recordData = $this->_db->loadObject();
            $this->_currentSectTtl = $this->sections[$this->_currentSection][1];
            
            // If this doesn't exist, create a dummy object with an invalid ID and return
            if(!$this->_recordData)
            {
               $this->_recordData = new stdClass();
               $this->_recordData->_id = 0;
            }
         }
         else
         {
            $this->setError('The requested table ['.$requestedSection.'] is unknown');
            $this->_recordData = null;
         }      
      }      
    }


     /**
     *   Returns the current section data
     */
    function getSectionData()
    {
      return $this->_sectionData;
    }
    
    
    /**
     *   Return the name of the current section
     */
    function getSectionName()
    {
      return $this->_currentSection;
    }
    
    /**
     *  Returns the reader-friendly section tytle
     */
    function getSectionTitle()
    {
        return $this->_currentSectTtl;
    }
    
    /**
     *   Returns the current record
     */
    function getRecordData()
    {
      return $this->_recordData;
    }
    
    /**
     *  Returns the sorting order array
     */
    function getDataSortOrder()
    {
      return $this->_sortOrder;
    }
    
    /**
     *  Returns the database name for a given catalog
     */
    function getDBName( $catalogName )
    {
      if( array_key_exists($catalogName,$this->sections) ){
        return $this->sections[$catalogName][0];
      } else {
        return "";
      }
    }
    
    
    /**
     *   Displays an error message
     */
    function setError( $errorMsg )
    {
      $app = &JFactory::getApplication();
      $app->enqueueMessage($errorMsg);
    }
        
    /**
     *   Stores recently updated information from a catalog table
     */
    function store()
    {
      // Get all the information stored on the form
      $data = JRequest::get('post');
      
      // Get the adequate table object to store this information
      $tableName = JRequest::getString('sectn');
      $row      =& $this->getTable( $tableName );
      
      // bind the fields into our table object. This attempts to grab all <input> tags
      // in the form, and match them with the table object's members
      if(!$row->bind($data)){
         $this->setError($this->_db->getErrorMsg());
         return false;
      }
        
      // Make sure the record is valid
      if(!$row->check()){
         $this->setError($this->_db->getErrorMsg());
         return false;
      }
        
      // Store the updated information. New records are automatically created
      // if the ID is zero
      if(!$row->store()){
         $this->setError($this->_db->getErrorMsg());
         return false;            
      }

      // Done!
      return true;
    }
  
   /**
    *    Deletes a record
    */
   function delete()
   {
      // Access the id (only thing we need) and our table object
      $ids = JRequest::getVar('cid',array(0), 'post', 'array');      

      // Get the adequate table object to store this information
      $tableName = JRequest::getString('sectn');
      $row      =& $this->getTable( $tableName );
              
      // Delete each one of them
      foreach($ids as $id)
      {
        if(!$row->delete($id))
        {
          $this->setError($row->getError());
          return false;
        }
      }
      
      return true;
   }
   
   
   /**
    * Counts the number of dependancies for a given table A over a table B, given
    * the item with id X. First and second tables are names as specified in the
    * keys of the $sections array
    * 
    * @param firstTable       The main table
    * @param secontTable      The table we have a dependancy with
    * @param idName           Name of the field in the table that links to the other table
    * @param depentandField   Name of the field in the dependant table
    * @param entryID          Id of the entry on the main table
    * @return The number of resulting records
    */
  function countDependancies( $firstTable, $secondTable, $idName, $dependantField, $entryID )
  {
    // The final count
    $result = 0;
    
    // Make sure these keys exist:
    if( array_key_exists($firstTable,$this->sections) && array_key_exists($secondTable,$this->sections) )
    {
      // Names of our actual tables
      $tableA = $this->sections[$firstTable][0];
      $tableB = $this->sections[$secondTable][0];
      
      // Query
      $query  = "SELECT ".$tableA.".".$idName." FROM ".$tableA." INNER JOIN ".$tableB;
      $query .= " ON ".$tableB.".".$dependantField." = ".$tableA.".".$idName;
      $query .= " WHERE ".$tableA.".".$idName." = ".$entryID;
      
      // How many records do we get back?
      $db   =& JFactory::getDBO();        
      $db->setQuery( $query );
      $db->query();
      
      return $db->getNumRows();
      
    }
    
  }
  
  /**
   *  Helper function: Returns all equipment pieces needed by a given song
   *  @param a_songID The id of the song
   *  @return An array of the form [equipment name, equipment type, equipment id]
   */
  function getEquipmentForSong( $a_songID )
  {
    $sql = "SELECT e.id, e.name, t.name ".
       "FROM #__Musashi_Equipment as e ".
       "INNER JOIN #__Musashi_EquipmentType as t ON e.type = t.id ".
       "WHERE e.id IN ".
        "(".
            "SELECT idEquipment ".
            "FROM #__Musashi_Song_Equipment ".
            "WHERE idSong = $a_songID".
        ")";
        
    $this->_db->setQuery( $sql );
    return $this->_db->loadRowList();

  }
   
  /**
   *  Helper function: Returns the needed amount of equipment pieces for a given
   *  song/type combination
   *  @param a_songID       The id of the song we are interested in
   *  @param a_equipmentID  id of the equipment we are counting
   *  @return Number of required pieces, or zero if not found
   */
  function getEquipmentCount( $a_songID, $a_equipmentID )
  {
    $sql = "SELECT quantity FROM #__Musashi_Song_Equipment ".
           "WHERE idEquipment = ".$a_equipmentID.
           " AND   idSong = ".$a_songID;
           
    $this->_db->setQuery( $sql );
    $count = $this->_db->loadResult();
    
    if( is_null($count) )
    {
      return 0;
    }
    else
    {
      return (int)$count;
    }
  }
  
  /**
   *  Helper function: Adds an entry into our song-equipment table. If the
   *  number of units is zero, this function removes the entry
   *  @param a_songID ID of the song we are adding
   *  @param a_eqID   ID of the equipment piece
   *  @param a_count  Number of units we are adding
   */
  function updateEquipment( $a_songID, $a_eqID, $a_count )
  {
    $count = (int)$a_count;
    
    if( $count == 0 )
    {
      $sql = "DELETE FROM #__Musashi_Song_Equipment WHERE idEquipment = ".$a_eqID." AND idSong = ".$a_songID;
    }
    else
    {
      $sql   = "REPLACE INTO #__Musashi_Song_Equipment (idEquipment, idSong, quantity) ".
               "VALUES ($a_eqID,$a_songID,$count)";      
    }
             
    $this->_db->setQuery( $sql );
    return $this->_db->query();
  }
  
  
 }
?>
